<?php
/**
 * This class is a base class for build SQL string class.
 * 
 * @author Micheal Luthor
 * @version 0.0.0
 *
 */

/**
 * @todo Support all sql key words
 */

class XSqlBuilder{
	/**
	 * 
	 * @var string 
	 */
	private $sql;
	
	/**
	 * @param $option integer 
	 *
	 * @return SqlBuilder
	 * @since 0.0.0
	 */
	public function insert($option=0){
		$this->sql = 'INSERT ';
		return $this;
	}
	
	/**
	 * @param $tableName string
	 * @param $columnNames array
	 * 		  array('column1', 'column2',...)
	 * 
	 * @return SqlBuilder
	 * @since 0.0.0
	 */
	public function into($tableName, $columnNames=null){
		$into = ' INTO '.$this->quoteSimpleTableName($tableName);
		
		if ( null != $columnNames && is_array($columnNames)){
			$columnNamesCount = count($columnNames);
			for ( $i=0; $i<$columnNamesCount; $i++ ){
				$columnNames[$i] = $this->quoteSimpleColumnName($columnNames[$i]);
			}
			
			$columnNames = '('.implode(',', $columnNames).')';
		}
		else {
			$columnNames = '';
		}
		
		$this->sql .= ($into.$columnNames);
		
		return $this;
	}
	
	/**
	 * @param $values array 
	 * <pre>
	 * array(
	 *　　array(1, 'string1', '2005-12-15'),
	 *　　array(1, 'string1', '2005-12-15')
	 *　)
	 * </pre>
	 * 
	 * @return SqlBuilder
	 * @since 0.0.0
	 */
	public function values($values){
		$valuesCount = count($values);
		for ( $i=0; $i<$valuesCount; $i++ ){
			$columnsCount = count($values[$i]);
			for ( $j=0; $j<$columnsCount; $j++ ){
				if ( !is_numeric($values[$i][$j]) )
					$values[$i][$j] = $this->quoteAndEascapSimpleString($values[$i][$j]);
			}
			
			$values[$i] = '('.implode(',', $values[$i]).')';
		}
		
		$values = implode(',', $values);
		
		$this->sql .= (' VALUES '.$values);
		return $this;
	}
	
	/**
	 * @param $columnNames array array('column1', 'column2',...)
	 * 
	 * @return SqlBuilder
	 * @since 0.0.0
	 */
	public function select($columnNames = null){
		if ( null == $columnNames ){
			$columnNames = '*';
		}
		else {
			foreach ( $columnNames as $key => $value ){
				$columnNames[$key] = $this->quoteSimpleColumnName($value);
			}
			
			$columnNames = implode(',', $columnNames);
		}
		
		$this->sql = ' SELECT '.$columnNames;
		
		return $this;
	}
	
	/**
	 * @param $tableNames array array('table1', 'table2',...)
	 * 
	 * @return SqlBuilder
	 * @since 0.0.0
	 */
	public function from($tableNames){
		foreach ( $tableNames as $key => $value ){
			$tableNames[$key] = $this->quoteSimpleTableName($value);
		}
		
		$tableNames = implode(',', $tableNames);
		
		$this->sql .= (' FROM '.$tableNames);
		
		return $this;
	}
	
	/**
	 * @param $whereDefinition string
	 * 
	 * @return SqlBuilder
	 * @since 0.0.0
	 */
	public function where($whereDefinition){
		$this->sql .= (' WHERE '.$whereDefinition);
		
		return $this;
	}
	
	/**
	 * @param $orderBy array array(array('column1', 'DESC'), array('column2', 'asc'))
	 * 
	 * @return SqlBuilder
	 * @since 0.0.0
	 */
	public function orderBy($orderBy){
		foreach ($orderBy as $key => $value ){
			$orderBy[$key][0] = $this->quoteSimpleColumnName($orderBy[$key]);
			$orderBy[$key] = implode(' ', $orderBy[$key]);
		}
		$orderBy = implode(' ', $orderBy);
		
		$this->sql .= (' ORDER BY '.$orderBy);
		
		return $this;
	}
	
	/**
	 * @param $rowCount integer 
	 * @param $offset integer
	 * 
	 * @return SqlBuilder
	 * @since 0.0.0
	 */
	public function limit($rowCount, $offset=0){
		$this->sql .= (' LIMIT '.$offset.', '.$rowCount);
		
		return $this;
	}
	
	/**
	 * @param $tableName string 
	 * @param $option integer
	 * 
	 * @return SqlBuilder
	 * @since 0.0.0
	 */
	public function update($tableName, $option = 0){
		$this->sql = ' UPDATE '.$tableName;
		
		return $this;
	}
	
	/**
	 * @param $values array array('column1'=>1, 'column'=>'www')
	 * 
	 * @return SqlBuilder
	 * @since 0.0.0
	 */
	public function set($values){
		foreach ($values as $key => $value ){
			if ( !is_numeric($value) )
				$value = $this->quoteAndEascapSimpleString($value);
			
			array_push($values, $key.'='.$value);
			unset($values[$key]);
		}
		
		$values = implode(',', $values);
		
		$this->sql .= (' SET '.$values);
		
		return $this;
	}
	
	/**
	 * @param $option integer
	 * 
	 * @return SqlBuilder
	 * @since 0.0.0
	 */
	public function delete($option=0){
		$this->sql .= ' DELETE ';
		
		return $this;
	}
	
	/**
	 * @param $tabelName string
	 * 
	 * @return SqlBuilder
	 * @since 0.0.0
	 */
	public function truncate($tabelName){
		$this->sql .= ' TRUNCATE '.$tabelName;
		
		return $this;
	}
	
	/**
	 * Get the sql string.
	 * 
	 * @return string
	 * @since 0.0.0
	 */
	public function getSQL(){
		return $this->sql;
	}
	
	/**
	 * Build sql string directly.
	 * 
	 * return SqlBuilder
	 * @since 0.0.0
	 */
	public function setSQL($sql){
		$this->sql = $sql;
		
		return $this;
	}
	
	/**
	 * @param $tableName string
	 * 
	 * @return string
	 * @since 0.0.0
	 */
	private function quoteSimpleTableName($tableName){
		return '`'.$tableName.'`';
	}

	/**
	 * @param $columnName string
	 * 
	 * @return string
	 * @since 0.0.0
	 */
	private function quoteSimpleColumnName($columnName){
		return '`'.$columnName.'`';
	}
	
	/**
	 * @param $string string
	 * 
	 * @return string
	 * @since 0.0.0
	 */
	private function quoteSimpleString($string){
		return '\''.$string.'\'';
	}
	
	/**
	 * @param $string string
	 * 
	 * @return string
	 * @since 0.0.0
	 */
	private function eascapSimpleString($string){
		return mysql_real_escape_string($string);
	}
	
	/**
	 * @param $string string
	 * 
	 * @return string
	 * @since 0.0.0
	 */
	private function quoteAndEascapSimpleString($string){
		return $this->quoteSimpleString($this->eascapSimpleString($string));
	}
}